package com.LGF.Book.dao;

import com.LGF.Book.bean.Record;
import com.LGF.Book.util.DBHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class RecordDao {

    //创建QueryRunner对象
    QueryRunner runner = new QueryRunner();

    /**
     * 根据用户的会员编号查询会员借阅信息
     * @param memId
     * @return
     * @throws SQLException
     */
    public List<Record> getRecordByMemberId(long memId) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from record where memberId =?  and backDate is null and rentDate is not null ";
        List<Record> records = runner.query(conn, sql, new BeanListHandler<Record>(Record.class), memId);
        DBHelper.close(conn);
        return records;

    }

    /**
     * 根据会员id和图书id查询借阅信息
     * @param memId
     * @param bookId
     * @return
     * @throws SQLException
     */
    public Record getById(long memId,long bookId ) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql="select * from record where memberId =? and bookId=? and backDate is null";
        Record record = runner.query(conn, sql, new BeanHandler<Record>(Record.class), memId, bookId);
        DBHelper.close(conn);
        return record;
    }

    /**
     * 根据会员id获取记录信息
     * @param memId
     * @return
     * @throws SQLException
     */
    public List<Record> getByIds(long memId) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql="select * from record where memberId =?  and backDate is null";
        List<Record> records = runner.query(conn, sql, new BeanListHandler<Record>(Record.class), memId);
        DBHelper.close(conn);
        return records;
    }

    /**
     * 添加一条借阅记录
     * @param memberId
     * @param bookId
     * @param deposit
     * @param userId
     * @return
     * @throws SQLException
     */
    public int add(long memberId,long bookId,float deposit,long userId) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="insert into record values(null,?,?,CURRENT_DATE(),null,?,?)";
        int count = runner.update(conn, sql, memberId, bookId, deposit, userId);
        DBHelper.close(conn);
        return count;
    }

    /**
     * 根据会员id获取所有会员信息
     * @return
     * @throws SQLException
     */
    public List<Record> getAllMemId(long memId) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql = "select * from record where memberId=?";
        List<Record> records = runner.query(conn, sql, new BeanListHandler<Record>(Record.class),memId);
        DBHelper.close(conn);
        return records;

    }


    /**
     * 根据会员id分页查询借阅信息
     * @param memId
     * @param page
     * @param limit
     * @return
     * @throws SQLException
     */
    public List<Record> getPageByMemId(long memId,int page,int limit) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from record where memberId=? limit ?,? ";
        List<Record> records = runner.query(conn, sql, new BeanListHandler<Record>(Record.class), memId, page, limit);
        DBHelper.close(conn);
        return records;
    }

    /**
     * 根据id更新归还时间
     * @param id
     * @return
     * @throws SQLException
     */
    public int modify(long id) throws SQLException {
        String sql="update record set rentDate= CURRENT_DATE where id=?";
        Connection conn = DBHelper.getConnection();
        int count = runner.update(conn, sql,id);
        DBHelper.close(conn);
        return count;
    }

    /**
     * 根据id获取记录表信息
     * @param recordId
     * @return
     * @throws SQLException
     */
    public Record getById(long recordId) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from record where id= ?";
        Record record = runner.query(conn,sql,new BeanHandler<Record>(Record.class),recordId);
        DBHelper.close(conn);
        return record;

    }

    /**
     * 根据id更改押金和操作员id，backDate归还时间
     * @param deposit
     * @param userId
     * @param id
     * @return
     * @throws SQLException
     */
    public int modify(double deposit,long userId,long id) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="update record set backDate =CURRENT_DATE,deposit=?,userId=? where id=?";
        int count = runner.update(conn, sql, deposit, userId, id);
        DBHelper.close(conn);
        return count;


    }

    /**
     * 多条件查询记录信息
     * @param search
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> conditionSearch(String search) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from recordView where memberName like '%' ? '%' or mtname like '%' ? '%' or bookName like '%' ? '%' or tname like '%' ? '%' or rentDate like '%' ? '%' or backDate like '%' ? '%'";
        List<Map<String, Object>> data = runner.query(conn, sql, new MapListHandler(), search, search, search, search, search, search);
        DBHelper.close(conn);
        return data;

    }

    /**
     * 多条件分页查询
     * @param page
     * @param limit
     * @param search
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> conditionSearchByPage(int page ,int limit,String search) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from recordView where memberName like '%' ? '%' or mtname like '%' ? '%' or bookName like '%' ? '%' or tname like '%' ? '%' or rentDate like '%' ? '%' or backDate like '%' ? '%' limit ?,?";
        List<Map<String, Object>> data = runner.query(conn, sql, new MapListHandler(), search, search, search, search, search, search,page,limit);
        DBHelper.close(conn);
        return data;
    }

    /**
     * 查询所有记录信息
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> searchAll() throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from recordView ";
        List<Map<String, Object>> data = runner.query(conn, sql, new MapListHandler());
        DBHelper.close(conn);
        return data;
    }

    /**
     * 分页查询记录信息
     * @param page
     * @param limit
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> getByPage(int page,int limit) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from recordView limit ?,?  ";
        List<Map<String, Object>> data = runner.query(conn, sql, new MapListHandler(),page,limit);
        DBHelper.close(conn);
        return data;
    }

    /**
     * 根据id分页查询会员借阅归还信息
     * @param memId
     * @param page
     * @param limit
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> getByPageMemId( long memId,int page,int limit) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from recordView where memberId=? limit ?,?  ";
        List<Map<String, Object>> data = runner.query(conn, sql, new MapListHandler(),memId,page,limit);
        DBHelper.close(conn);
        return data;
    }

    /**
     * 根据会员id查询会员的所有借阅归还信息
     * @param memId
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> selectByMemId(long memId) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from recordView where memberId=? ";
        List<Map<String, Object>> data = runner.query(conn, sql, new MapListHandler(),memId);
        DBHelper.close(conn);
        return data;
    }



}
